﻿if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RemindersQueueDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].RemindersQueueDelete
GO

CREATE PROCEDURE dbo.RemindersQueueDelete
(
	@RemindersQueueID int
)
AS

SET NOCOUNT ON

-- First save the records to audit tables
INSERT INTO dbo.RejectedRemindersQueue
(
	RemindersQueueID,
	RemindersQueueName,
	SiteCollectionID,
	SiteCollectionJobIdentifier,
	ApprovalRequestAttempts,
	Approved,
	IsBeingProcessed,
	Completed,
	HasErrors,
	Created,
	Modified
)
SELECT
	RemindersQueueID,
	RemindersQueueName,
	SiteCollectionID,
	SiteCollectionJobIdentifier,
	ApprovalRequestAttempts,
	Approved,
	IsBeingProcessed,
	Completed,
	HasErrors,
	Created,
	Modified
FROM
	RemindersQueue
WHERE
	RemindersQueueID = @RemindersQueueID
	
INSERT INTO dbo.RejectedRemindersQueueItem
(
	RemindersQueueItemID,
	RemindersQueueID,
	SiteID,
	SiteTitle,
	SiteUrl,
	FromAddress,
	PreservationNoticeTemplateID,
	OriginalPreservationNoticeHistoryID,	
	MessageSubject,
	ReminderType,
	CustodianID,
	CustodianEmail,
	CustodianFirstName,
	CustodianLastName,
	ProcessingAttempts,
	Completed,
	HasErrors,
	Errors,
	Created,
	Modified
)
SELECT
	RemindersQueueItemID,
	RemindersQueueID,
	SiteID,
	SiteTitle,
	SiteUrl,
	FromAddress,
	PreservationNoticeTemplateID,
	OriginalPreservationNoticeHistoryID,	
	MessageSubject,
	ReminderType,
	CustodianID,
	CustodianEmail,
	CustodianFirstName,
	CustodianLastName,
	ProcessingAttempts,
	Completed,
	HasErrors,
	Errors,
	Created,
	Modified
FROM
	RemindersQueueItem
WHERE
	RemindersQueueID = @RemindersQueueID

-- Now delete the records
DELETE
FROM
	RemindersQueueItem
WHERE
	RemindersQueueID = @RemindersQueueID
	
DELETE

FROM 
	RemindersQueue
WHERE
	RemindersQueueID = @RemindersQueueID
GO 